import sqlite3
from sqlite3.dbapi2 import DatabaseError
from utils.log_output import simple_text_log
import numpy as np
import os


class DBReader:
    def __init__(self, db_file: str):
        self.db_file = db_file

        try:
            f = open(os.path.join('DB', 'last_db.txt'), 'rt')
            new_db_file = f.read().rstrip()
            self.switch_to_db_file(new_db_file)
        except Exception as err:
            simple_text_log('frontend', str(err))

        try:
            self.load_hot_book()
        except Exception as err:
            simple_text_log('frontend', str(err))
            raise DatabaseError("Error when loading hot book data")

    def get_conn(self):
        self.conn = sqlite3.connect(self.db_file)
        return self.conn

    def close_conn(self):
        if self.conn is None:
            return

        try:
            self.conn.close()
        except Exception as err:
            simple_text_log('frontend', str(err))

        self.conn = None

    def check_db(self, new_db: str):
        try:
            conn = sqlite3.connect(new_db)
            conn.execute('SELECT * FROM hot_book')
            return True
        except Exception as err:
            simple_text_log('frontend', str(err))
            return False
        finally:
            conn.close()

    def load_hot_book(self):
        try:
            sql = f'SELECT * FROM hot_book'
            conn = self.get_conn()
            cursor = conn.cursor()
            rows = cursor.execute(sql)

            result_i = []
            result_p = []

            for row in rows:
                result_i.append(row[0])
                result_p.append(row[1])

            if len(result_i) == 0:
                raise ValueError("No hot book")

            sum_p = np.sum(result_p)
            result_p = [i / sum_p for i in result_p]

            self.hot_book_i = result_i
            self.hot_book_p = result_p

            # Get book info
            self.hot_book_info = {}
            for book_id in self.hot_book_i:
                sql = f'SELECT * FROM book_info WHERE book_id = {book_id}'
                rows = cursor.execute(sql)

                result = None
                for row in rows:
                    result = {
                        'book_id': book_id,
                        'isbn': row[1],
                        'code': row[2],
                        'title': row[3]
                    }

                if result is None:
                    raise ValueError(f"No book_info for book_id {book_id}")

                self.hot_book_info[book_id] = result

        except Exception as err:
            simple_text_log('frontend', str(err))
            raise DatabaseError("DB file connection error")
        finally:
            if cursor is not None:
                cursor.close()
            self.close_conn()

    def get_hot_book_recommend(self, max_cnt: int = 25):
        if len(self.hot_book_i) < max_cnt:
            raise ValueError(
                f"No enough hot books ({len(self.hot_book_i)}) for recommendation ({max_cnt})")

        rec_book = np.random.choice(
            self.hot_book_i, max_cnt, replace=False, p=self.hot_book_p)

        books = [self.hot_book_info[i] for i in rec_book]
        result = {
            'ok': True,
            'total': len(books),
            'books': books
        }

        return result

    def switch_to_db_file(self, new_db_file: str):
        if not self.check_db(new_db_file):
            simple_text_log('frontend', f'Invalid DB file: {new_db_file}. Use the old one.')
            return
        self.db_file = new_db_file
        with open(os.path.join('DB', 'last_db.txt'), 'wt') as f:
            f.write(f'{new_db_file}')
        simple_text_log('frontend', f'DB file switch to {new_db_file}')

    def get_recommend_with_username(self, username: str, max_cnt: int = 100):
        simple_text_log('frontend', f'Recommend with {self.db_file}')
        try:
            sql = f'SELECT user_id FROM name_to_user_id WHERE username = \'{username}\''
            conn = self.get_conn()
            cursor = conn.cursor()
            rows = cursor.execute(sql)

            user_id = None
            for row in rows:
                user_id = row[0]

            if user_id is None:
                simple_text_log('frontend', 
                    f"Username {username} not found. Fall back to hot recommendation.")
                return self.get_hot_book_recommend(max_cnt=max_cnt)

            sql = f'SELECT book_rec.book_id, book_info.isbn, book_info.code, book_info.title FROM book_rec, book_info WHERE book_rec.user_id = \'{user_id}\' AND book_info.book_id = book_rec.book_id'
            rows = cursor.execute(sql)

            rec_book = []
            for row in rows:
                rec_book.append({
                    'book_id': row[0],
                    'isbn': row[1],
                    'code': row[2],
                    'title': row[3]
                })

            if len(rec_book) < max_cnt:
                simple_text_log('frontend', 
                    f"No enough recommendation results for {username}. Fall back to hot recommendation")
                return self.get_hot_book_recommend(max_cnt=max_cnt)

            # Personalized Recommendation
            # Sample max_cnt items for user_id
            p = range(len(rec_book), 0, -1)
            p_sum = np.sum(p)
            p = [i / p_sum for i in p]
            rec_book_id = np.random.choice(
                range(len(rec_book)), max_cnt, replace=False, p=p)
            rec_book = [rec_book[i] for i in rec_book_id]

            result = {
                'ok': True,
                'total': len(rec_book),
                'books': rec_book
            }

            return result
        except Exception as err:
            simple_text_log('frontend', str(err))
            raise ValueError()
        finally:
            if cursor is not None:
                cursor.close()
            self.close_conn()
